import {open} from 'sqlite';
import sqlite3 from 'sqlite3';
import path from 'node:path';
import {dirname} from 'path';
import {fileURLToPath} from 'node:url';
import fs from 'fs';

export async function connectDb(dbPath) {
    console.log('Connecting to database at path:', dbPath);

    if (!fs.existsSync(dbPath)) {
        console.error('Database file does not exist at path:', dbPath);
        throw new Error('Database file does not exist');
    } else {
        console.log('Database file exists at path:', dbPath);
    }

    const db = await open({
        filename: dbPath,
        driver: sqlite3.Database,
    });

    console.log('Database connection established.');
    return db;
}

const __filename = fileURLToPath(import.meta.url);
const __dirname = dirname(__filename);
const projectRoot = path.resolve(__dirname, '../..');
const userDbPath = path.join(projectRoot, 'boss-sqlite', 'boss-interception.db');
// const userDbPath = 'boss-interception.db'
export default async function operateDb() {
    console.log('------------user path ----------------', userDbPath);

    const db = await connectDb(userDbPath);

    console.log('------------connect db-------------');

    // 定义查询函数，接受 SQL 查询语句和可选的参数数组
    function query(sql, params = []) {
        // 返回一个新的 Promise 对象
        return new Promise((resolve, reject) => {
            // 创建一个 SQLite3 数据库连接
            const db = new sqlite3.Database(userDbPath, function (err) {
                if (err) {
                    db.close();
                    reject(err); // 连接错误时，reject Promise 并传递错误信息
                    return;
                }

                // 执行查询语句
                db.all(sql, params, function (err, rows) {
                    // 查询完成后，关闭数据库连接
                    db.close(function (closeErr) {
                        if (closeErr) {
                            console.error('Error closing database:', closeErr.message);
                        }
                    });

                    if (err) {
                        reject(err); // 查询出错时，reject Promise 并传递错误信息
                    } else {
                        resolve(rows); // 查询成功时，resolve Promise 并传递查询结果数组
                    }
                });
            });
        });
    }


    async function run(sql, params = []) {
        console.log('Executing run:', sql, 'with params:', params);
        return new Promise((resolve, reject) => {
            db.run(sql, params, function (err) {
                if (err) {
                    console.error('Run error:', err);
                    reject(err);
                } else {
                    console.log('Run result:', this);
                    resolve();
                }
            });
        });
    }

    async function select(sql, params = []) {
        try {
            console.log('---log query --');
            console.log('---sql --', sql);
            const rows = await query(sql, params);
            console.log('res:', rows);
            return rows;
        } catch (error) {
            console.error('select fail:', error);
            throw error;
        }
    }

    async function insert(sql, params = []) {
        try {
            await run(sql, params);
            console.log('insert success');
        } catch (error) {
            console.error('insert fail:', error);
        }
    }

    async function update(sql, params = []) {
        try {
            await run(sql, params);
            console.log('update success');
        } catch (error) {
            console.error('update fail:', error);
            throw error;
        }
    }

    async function remove(sql, params = []) {
        try {
            await run(sql, params);
            console.log('删除成功');
        } catch (error) {
            console.error('删除错误:', error);
            throw error;
        }
    }

    return {
        select,
        insert,
        update,
        remove,
        query
    };
}

async function main() {
    const option = operateDb()
    option.query('SELECT * FROM job_logs')
}

// 执行主函数
main();
